package edu.gatech.cs2340.tigershroud;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Handles all database administration calls for the User database.
 * @author 
 *
 */
public class TodoDataSource 
{
	private SQLiteDatabase tododb;
	private TodoSQLiteHelper dbHelper;
	private MasterControlClass mcc = new MasterControlClass();
	private String[] allColumns = 
			{ 	
				TodoSQLiteHelper.COLUMN_ID,
				TodoSQLiteHelper.COLUMN_UNAME, 
				TodoSQLiteHelper.COLUMN_TNAME, 
				TodoSQLiteHelper.COLUMN_DATE, 
				TodoSQLiteHelper.COLUMN_TIME, 
				TodoSQLiteHelper.COLUMN_DNUM, 
				TodoSQLiteHelper.COLUMN_DESCR,
				TodoSQLiteHelper.COLUMN_CATE,
				TodoSQLiteHelper.COLUMN_ADDR,
				TodoSQLiteHelper.COLUMN_CITY,
				TodoSQLiteHelper.COLUMN_STATE,
				TodoSQLiteHelper.COLUMN_ZIP,
				TodoSQLiteHelper.COLUMN_STAT,
				TodoSQLiteHelper.COLUMN_PRIOR
			};

	/**
	 * Constructor for the datasource the context is received from the application
	 * @param context
	 */
	public TodoDataSource(Context context) 
	{
		dbHelper = new TodoSQLiteHelper(context);
	}

	/**
	 * Gets a database that the app can write to.
	 * @throws SQLException if there is no database to open
	 */
	public void open() throws SQLException 
	{
		tododb = dbHelper.getWritableDatabase();
	}
	/**
	 * Closes the database after use.
	 */
	public void close() 
	{
		dbHelper.close();
	}
	
	/**
	 * Creates a task and puts the necessary info in the database.
	 * @param name The task's name
	 * @param date The task's date
	 * @param time The task's time
	 * @param long dnum The task's id number
	 * @param des The task's description
	 * @param addr The task's address
	 * @param city The task's city it is located in
	 * @param state The task's state it is located in
	 * @param zip	The task's zip code
	 * @param stat The task's status can be complete or incomplete
	 * @return The Task object of the newly created task.
	 */
	public Task createTask(String tnam, String dat, String tim, long dnum, String des, String cat, String addr, String city, String state, String zip, String stat, String prior) 
	{
		ContentValues values = new ContentValues();
		values.put(TodoSQLiteHelper.COLUMN_UNAME, mcc.getUname());
		values.put(TodoSQLiteHelper.COLUMN_TNAME, tnam);
		values.put(TodoSQLiteHelper.COLUMN_DATE, dat);
		values.put(TodoSQLiteHelper.COLUMN_TIME, tim);
		values.put(TodoSQLiteHelper.COLUMN_DNUM, dnum);
		values.put(TodoSQLiteHelper.COLUMN_DESCR, des);
		values.put(TodoSQLiteHelper.COLUMN_CATE, cat);
		values.put(TodoSQLiteHelper.COLUMN_ADDR, addr);
		values.put(TodoSQLiteHelper.COLUMN_CITY, city);
		values.put(TodoSQLiteHelper.COLUMN_STATE, state);
		values.put(TodoSQLiteHelper.COLUMN_ZIP, zip);
		values.put(TodoSQLiteHelper.COLUMN_STAT, stat);
		values.put(TodoSQLiteHelper.COLUMN_PRIOR, prior);
		
		long insertId = tododb.insert(TodoSQLiteHelper.TABLE_TODO, null, values);
		Log.v("ID is", insertId + "");
		
		Cursor cursor = tododb.query(TodoSQLiteHelper.TABLE_TODO, allColumns, TodoSQLiteHelper.COLUMN_ID + " = " 
		+ insertId, null, null, null, null);
		
		Log.d("TODO", "creating a new task --- user: " + mcc.getUname());
		cursor.moveToFirst();
		Task t = cursorToTask(cursor);
		cursor.close();
		return t;
	}
	
	/**
	 * Queries the database for a task.
	 * @param col The column of the desired attribute for which we are searching.
	 * @param s The String to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> search(String col, String s, String o, String t)
	{
		String temp;
		if(t.equals("super"))
		{
			temp = "SELECT * FROM todo WHERE " + col + " = '" + s + "' ORDER BY task " + o;
		}
		else
		{
			temp = "SELECT * FROM todo WHERE user = '" + mcc.getUname() 
					+ "' AND " + col + " = '" + s + "' ORDER BY task " + o;
		}
		return createTaskList(tododb.rawQuery(temp, null));
	}
	
	/**
	 * Queries the database for a task based on the username
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByCurrentUserName(String o) 
	{
		Log.d("TODO", "mcc: " + mcc.getUname());
		String temp = "SELECT * FROM todo WHERE user = '" + mcc.getUname() + "' ORDER BY task " + o;
		Cursor cursor = tododb.rawQuery(temp, null);
		return createTaskList(cursor);
	}
	
	/**
	 * Queries the database for a task based on category.
	 * @param cat The category to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByCategory(String cat, String o, String t) 
	{
		return search(TodoSQLiteHelper.COLUMN_CATE, cat, o, t);
	}

	/**
	 * Queries the database for a task.
	 * @param dat The date to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByDate(String dat, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_DATE, dat, o, t);
	}
	
	/**
	 * Queries the database for a task based on time.
	 * @param tim The time to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByTime(String tim, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_TIME, tim, o, t);
	}
	
	/**
	 * Queries the database for a task based on description.
	 * @param des The description to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */	
	public List<Task> searchByDescription(String des, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_DESCR, des, o, t);
	}
	
	/**
	 * Queries the database for a task based on Task name.
	 * @param tnam The task to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByTaskName(String tnam, String o, String t) 
	{
		return search(TodoSQLiteHelper.COLUMN_TNAME, tnam, o, t);
	}
	
	/**
	 * Queries the database for a task based on status.
	 * @param s The status to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByStatus(String stat, String o, String t) 
	{
		return search(TodoSQLiteHelper.COLUMN_STAT, stat, o, t);
	}
	/**
	 * Queries the database for a task based on address.
	 * @param stat The address to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByAddress(String addr, String o, String t) 
	{
		return search(TodoSQLiteHelper.COLUMN_ADDR, addr, o, t);
	}
	
	/**
	 * Queries the database for a task based on city.
	 * @param city The city to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByCity(String city, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_CITY, city, o, t);
	}
	
	/**
	 * Queries the database for a task based on state.
	 * @param col The column of the desired attribute for which we are searching.
	 * @param state The String to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByState(String state, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_STATE, state, o, t);
	}
	
	/**
	 * Queries the database for a task based on zip code.
	 * @param col The column of the desired attribute for which we are searching.
	 * @param zip The zip code to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> searchByZipCode(String zip, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_ZIP, zip, o, t);
	}
	
	public List<Task> searchByPriority(String prior, String o, String t)  
	{
		return search(TodoSQLiteHelper.COLUMN_PRIOR, prior, o, t);
	}

	/**
	 * Queries the database for a task.
	 * @param dat The date to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> beforeDate(String dat, String o, String t)  
	{
		String temp; 
		if(t.equals("super"))
		{
			temp = "SELECT * FROM todo WHERE dnum < " + Long.parseLong(dat) + " ORDER BY task " + o;
		}
		else
		{
			temp = "SELECT * FROM todo WHERE user = '" + mcc.getUname() 
					+ "' AND dnum < " + Long.parseLong(dat) + " ORDER BY task " + o;
		}
		Cursor cursor = tododb.rawQuery(temp, null);
		return createTaskList(cursor);
	}
	
	/**
	 * Queries the database for a task.
	 * @param date The String to search for
	 * @param o The String to order the results by
	 * @return A list of matching tasks. Otherwise, null.
	 */
	public List<Task> afterDate(String dat, String o, String t)
	{
		String temp;
		long date = Long.parseLong(dat);
		if(t.equals("super"))
		{
			temp = "SELECT * FROM todo WHERE dnum > " + date + " ORDER BY task " + o;
		}
		else
		{
			temp = "SELECT * FROM todo WHERE user = '" + mcc.getUname() 
					+ "' AND dnum > " + date + " ORDER BY task " + o;
		}
		Cursor cursor = tododb.rawQuery(temp, null);
		return createTaskList(cursor);
	}
	
	/**
	 * Creates a new task list from the results of a cursor
	 * @param cursor The cursor object that holds the results of a query 
	 * @return A list of tasks. Otherwise, null.
	 */
	public List<Task> createTaskList(Cursor cursor)
	{
		List<Task> tl = new ArrayList<Task>();
		if(cursor.moveToFirst())
		{
			while (!cursor.isAfterLast()) 
			{
				Task t = cursorToTask(cursor);
				tl.add(t);
				cursor.moveToNext();
			}
		}
		
		if (tl.isEmpty())
		{
			Task nullTask = new Task();
			nullTask.setTaskName("No tasks exist in the todo list for " + mcc.getUname() + ".");
			nullTask.setDnum(-1);
			tl.add(nullTask);
		}
		cursor.close();
		return tl;
	}
	
	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	List<Task> getAllTask(String o) 
	{
		String temp = "SELECT * FROM todo ORDER BY user " + o;
		Cursor cursor = tododb.rawQuery(temp, null);
		return createTaskList(cursor);
	}
	
	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public List<Task> getAllTaskForUser(String o) 
	{
		return searchByCurrentUserName(o);
	}
	
	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public Task getFirstTask(String t)
	{
		if(t.equals("super"))
		{
			return getAllTask("ASC").get(0);
		}
		return getAllTaskForUser("ASC").get(0);
	}
	
	/**
	 * Gets a task that was created recently.
	 * @return most recently created task. Otherwise, null.
	 */ 
	public Task getLastTask(String t)
	{
		List<Task> temp;
		if(t.equals("super"))
		{
			temp = getAllTask("ASC");
		}
		else
		{
			temp = getAllTaskForUser("ASC");
		}
		
		int last = temp.size()-1;
		return temp.get(last);
	} 
	
	/**
	 * Creates a new task list that contains all completed tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public List<Task> getAllCompleteTask(String t, String o) 
	{
		return searchByStatus("1", o, t);
	}
	
	/**
	 * Creates a new task list that contains all incomplete tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public List<Task> getAllIncompleteTask(String t, String o) 
	{
		return searchByStatus("0", o, t);
	}
	
	public List<Task> getAllHigh(String t, String o) 
	{
		return searchByPriority("High", o, t);
	}
	
	public List<Task> getAllMedium(String t, String o) 
	{
		return searchByPriority("Medium", o, t);
	}
	
	public List<Task> getAllLow(String t, String o) 
	{
		return searchByPriority("Low", o, t);
	}
	
	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public void deleteTask(Task t) 
	{
		String temp = "DELETE FROM todo WHERE _id = '" + t.getID() + "'";
		tododb.execSQL(temp);
	}

	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public Task cursorToTask(Cursor cursor) 
	{
		Task task = new Task();
		task.setID(cursor.getLong(0));
		task.setUserName(cursor.getString(1));
		task.setTaskName(cursor.getString(2));
		task.setDate(cursor.getString(3));
		task.setTime(cursor.getString(4));
		task.setDnum(cursor.getLong(5));
		task.setDescription(cursor.getString(6));
		task.setCategory(cursor.getString(7));
		task.setAddress(cursor.getString(8));
		task.setCity(cursor.getString(9));
		task.setState(cursor.getString(10));
		task.setZipCode(cursor.getString(11));
		task.setStatus(cursor.getString(12));
		task.setPriority(cursor.getString(13));
		return task;
	}
	/**
	 * Creates a new task list that contains all tasks,
	 * @return A list of tasks. Otherwise, null.
	 */ 
	public void edit(Task t, String col, String s)
	{
		String temp = "UPDATE todo SET " + col + " = '" + s + "' WHERE  _id = '" + t.getID() + "'";
		tododb.execSQL(temp);
	}
	
	public void editTaskName(Task t, String name)
	{
		edit(t, TodoSQLiteHelper.COLUMN_TNAME, name);
	}
	
	public void editDate(Task t, String dat)
	{
		edit(t, TodoSQLiteHelper.COLUMN_DATE, dat);
	}
	
	public void editTime(Task t, String tim)
	{
		edit(t, TodoSQLiteHelper.COLUMN_TIME, tim);
	}

	public void editDnum(Task t, long dnu)
	{
		String temp = "UPDATE todo SET " + TodoSQLiteHelper.COLUMN_DNUM + " = " + dnu
				+ " WHERE  _id = '" + t.getID() + "'";
		tododb.execSQL(temp);
	}
	
	public void editDescription(Task t, String des)
	{
		edit(t, TodoSQLiteHelper.COLUMN_DESCR, des);
	}
	
	public void editCategory(Task t, String cat)
	{
		edit(t, TodoSQLiteHelper.COLUMN_CATE, cat);
	}
	
	public void editStatus(Task t, String stat)
	{
		edit(t, TodoSQLiteHelper.COLUMN_STAT, stat);
	}
	
	public void editAddress(Task t, String addr)
	{
		edit(t, TodoSQLiteHelper.COLUMN_ADDR, addr);
	}
	
	public void editCity(Task t, String city)
	{
		edit(t, TodoSQLiteHelper.COLUMN_CITY, city);
	}
	
	public void editState(Task t, String state)
	{
		edit(t, TodoSQLiteHelper.COLUMN_STATE, state);
	}
	
	public void editZipCode(Task t, String zip)
	{
		edit(t, TodoSQLiteHelper.COLUMN_ZIP, zip);
	}
	
	public void editPriority(Task t, String prior)
	{
		edit(t, TodoSQLiteHelper.COLUMN_PRIOR, prior);
	}
	
	public String taskCount(String t)
	{
		String temp;
		if(t.equals("super"))
		{
			temp = "SELECT COUNT(_id) FROM todo";
		}
		else
		{
			temp = "SELECT COUNT(_id) FROM todo WHERE user = '" + mcc.getUname() + "'";
		}
		
		Cursor cursor = tododb.rawQuery(temp, null);
		cursor.moveToFirst();
		String result = cursor.getString(0);
		cursor.close();
		return result;
	}
	
	public void insertTask(Task t)
	{
		createTask(t.getTaskName(),
				t.getDate(),
				t.getTime(),
				t.getDnum(),
				t.getDescription(),
				t.getCategory(),
				t.getAddress(),
				t.getCity(),
				t.getState(),
				t.getZipCode(),
				t.getStatus(),
				t.getPriority());
	}
	
	public void wipeTasksDB()
	{
		dbHelper.onUpgrade(tododb, 0, 0);
	}
}
